package com.jinmdz.fmis.common.util.third;

import com.jinmdz.fmis.common.model.PoiCell;
import com.jinmdz.fmis.common.model.PoiRow;
import com.jinmdz.fmis.common.model.PoiTable;
import com.jinmdz.fmis.common.model.PoiTitle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;

/**
 * poi 工具类
 *
 * @author LiCongLu
 * @date 2020-01-02 13:10
 */
public final class PoiUtil {

    /**
     * xlsx后缀名
     */
    public static final String EXCEL2007 = ".xlsx";

    /**
     * 生成一个Workbook
     *
     * @param poiTable 数据表
     * @param titles   标题
     * @return
     * @author LiCongLu
     * @date 2020-01-03 10:19
     */
    public static XSSFWorkbook createXSSFWorkbook(PoiTable poiTable, PoiTitle... titles) {

        // 创建工作簿，对应整个xlsx文件
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 创建sheet，对应excel的单个sheet
        XSSFSheet sheet = workbook.createSheet(poiTable.getSheetName());
        int columnSize = 0;

        // 将标题集合起来
        ArrayList<PoiTitle> poiTitles = new ArrayList<>();
        if (poiTable.getTitleCell() != null) {
            poiTitles.add(poiTable.getTitleCell());
        }
        if (titles != null && titles.length > 0) {
            Collections.addAll(poiTitles, titles);
        }

        // 创建标题
        if (poiTitles.size() > 0) {
            for (PoiTitle poiTitle : poiTitles) {
                if (poiTitle != null) {
                    if (poiTitle.getStyle() == null) {
                        poiTitle.setStyle(getXSSFHeaderStyle(workbook));
                    }
                    createPoiTitle(poiTitle, sheet);
                }
            }
        }

        int rowNum = poiTable.getColumnRowNum();
        // 创建列名
        if (poiTable.getColumnRow() != null) {
            createPoiRow(poiTable.getColumnRow(), sheet.createRow(rowNum++), getXSSFColumnStyle(workbook));
            columnSize = poiTable.getColumnRow().getCells().size();
        }

        // 创建单元格
        XSSFCellStyle cellStyle = getXSSFCellStyle(workbook);
        if (poiTable.getRows() != null) {
            for (PoiRow poiRow : poiTable.getRows()) {
                if (poiRow != null && poiRow.getCells() != null && poiRow.getCells().size() > 0) {
                    createPoiRow(poiRow, sheet.createRow(rowNum++), cellStyle);
                }
            }
        }

        // 单元格调整宽度
        if (columnSize > 0) {
            for (int i = 0; i < columnSize; i++) {
                sheet.autoSizeColumn(i);
            }
        }
        return workbook;
    }

    /**
     * 初始化标题
     *
     * @param poiTitle 标题数据
     * @param sheet    表单
     * @return
     * @author LiCongLu
     * @date 2020-01-03 16:04
     */
    private static int createPoiTitle(PoiTitle poiTitle, XSSFSheet sheet) {
        int rowNum = poiTitle.getRowNum();
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        // 创建单元格，对应row中的一格
        XSSFCell cell = row.createCell(poiTitle.getColumnIndex());

        // 合并单元格
        if (poiTitle.getRangeAddress() != null) {
            sheet.addMergedRegion(poiTitle.getRangeAddress());
        }

        // 单元格设置值
        cell.setCellValue(poiTitle.getCellValue());
        // 单元格设置样式
        cell.setCellStyle(poiTitle.getStyle());

        return rowNum;
    }


    /**
     * 初始化数据行
     *
     * @param poiRow 行数据
     * @param row    表行
     * @param style  单元格样式
     * @return 新增行号
     * @author LiCongLu
     * @date 2020-01-03 10:40
     */
    private static int createPoiRow(PoiRow poiRow, XSSFRow row, XSSFCellStyle style) {
        for (PoiCell poiCell : poiRow.getCells()) {
            // 创建单元格，对应row中的一格
            XSSFCell cell = row.createCell(poiCell.getColumnIndex());
            // 单元格设置值
            cell.setCellValue(poiCell.getCellValue());
            // 单元格设置样式
            cell.setCellStyle(style);
        }
        return row.getRowNum();
    }

    /**
     * 初始化单元格样式
     *
     * @param workbook
     * @return
     * @author LiCongLu
     * @date 2020-01-03 10:40
     */
    public static XSSFCellStyle getXSSFCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();

        //居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //border
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);

        return style;
    }

    /**
     * 初始化列名单元格样式
     *
     * @param workbook
     * @return
     * @author LiCongLu
     * @date 2020-01-03 10:41
     */
    public static XSSFCellStyle getXSSFColumnStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = getXSSFCellStyle(workbook);
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        short height = 14;
        font.setFontHeightInPoints(height);
        style.setFont(font);
        return style;
    }

    /**
     * 初始化表头单元格样式
     *
     * @param workbook
     * @return
     * @author LiCongLu
     * @date 2020-01-03 16:03
     */
    public static XSSFCellStyle getXSSFHeaderStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = getXSSFCellStyle(workbook);
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        short height = 16;
        font.setFontHeightInPoints(height);
        style.setFont(font);
        return style;
    }

    /**
     * 从指定Excel文件里读取数据
     *
     * @param filePath  指定路径
     * @param index     表单索引
     * @param sheetName 表单名称，优先考虑
     * @return
     * @author LiCongLu
     * @date 2020-01-16 13:30
     */
    public static ArrayList<HashMap<String, Object>> readXLSXFile(String filePath, int index, String sheetName) {
        ArrayList<HashMap<String, Object>> dataTable = new ArrayList<>();
        try {

            //  文件不存在
            File file = new File(filePath);
            if (!file.exists()) {
                return null;
            }

            // 判断文件格式是否准确
            if (!filePath.endsWith(EXCEL2007)) {
                return null;
            }

            // 读取表格
            FileInputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            inputStream.close();

            //获取给定索引处的Sheet对象。
            XSSFSheet sheet = null;
            if (sheetName != null && sheetName.length() > 0) {
                sheet = workbook.getSheet(sheetName);
            } else {
                sheet = workbook.getSheetAt(index);
            }
            if (sheet == null) {
                return null;
            }

            //先返回XSSF和HSSF对象，再创建一个用于计算公式单元格的对象
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            // 遍历读取数据
            if (sheet.getLastRowNum() > 0) {
                Row columnRow = sheet.getRow(0);
                int columnNum = columnRow.getLastCellNum();
                ArrayList<String> columns = new ArrayList<>();
                for (int i = 0; i < columnNum; i++) {
                    Cell cell = columnRow.getCell(i);
                    String columnName = cell.getStringCellValue();
                    // 确保列明不为空
                    columns.add(columnName == null || columnName.length() == 0 ? "column" + i : columnName);
                }
                //取最后一行的行号
                int rowNum = sheet.getLastRowNum() + 1;
                for (int i = 1; i < rowNum; i++) {
                    Row row = sheet.getRow(i);
                    HashMap<String, Object> dataRow = new HashMap<>(16);
                    for (int j = 0; j < columnNum; j++) {
                        dataRow.put(columns.get(j).toLowerCase(), getCellObject(row.getCell(j), evaluator));
                    }
                    dataTable.add(dataRow);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return dataTable;
    }

    /**
     * 获取单元格对象值
     *
     * @param cell      单元格
     * @param evaluator 公式计算器
     * @return
     * @author LiCongLu
     * @date 2020-01-16 14:55
     */
    private static Object getCellObject(Cell cell, FormulaEvaluator evaluator) {
        Object object = null;
        try {
            CellValue cellValue = null;
            // 直接取值
            switch (cell.getCellType()) {
                case STRING:
                    object = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        object = cell.getDateCellValue();
                    } else {
                        object = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    object = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    object = null;
                    break;
                case FORMULA:
                    cellValue = evaluator.evaluate(cell);
                    break;
                default:
                    object = null;
                    break;
            }

            // 公式计算值
            if (cellValue != null && object == null) {
                switch (cellValue.getCellType()) {
                    case STRING:
                        object = cellValue.getStringValue();
                        break;
                    case NUMERIC:
                        object = cellValue.getNumberValue();
                        break;
                    case BOOLEAN:
                        object = cellValue.getBooleanValue();
                        break;
                    case ERROR:
                        object = null;
                        break;
                    default:
                        object = null;
                        break;
                }
            }
        } catch (Exception e) {

        }
        return object;
    }
}
